package manager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import entity.Student;

/**
 * An utility class to execute read queries, handle result sets and obtain
 * results in the desired entity object type.
 * 
 * @author
 * 
 */
class RetrieveStudentUtil {

	/**
	 * Convenient method to retrieve multiple students from database for the
	 * given query.
	 * 
	 * @param dbMgr
	 *            database manager object on which the query should be executed
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return an ArrayList of student objects with information retrieved from
	 *         the database
	 */
	static ArrayList<Student> retrieveMultipleStudentsForQuery(
			DatabaseMgr dbMgr, String sql, Object... param) {
		ArrayList<Student> students = new ArrayList<Student>();

		try {
			ResultSet rs = dbMgr.openAndRead(sql, param);
			while (rs.next()) {
				students.add(newStudentFromResultSet(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}

		return students;
	}

	/**
	 * Convenient method to retrieve a single student from database for the
	 * given query.
	 * 
	 * @param dbMgr
	 *            database manager object on which the query should be executed
	 * @param sql
	 *            the SQL statement to execute
	 * @param param
	 *            sequence of parameters to be used in the SQL statement
	 * @return a new student object with information retrieved from the database
	 */
	static Student retrieveStudentForQuery(DatabaseMgr dbMgr, String sql,
			Object... param) {
		Student student = null;

		try {
			ResultSet rs = dbMgr.openAndRead(sql, param);
			while (rs.next()) {
				student = newStudentFromResultSet(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}

		return student;
	}

	/**
	 * Convenient method to create a new student object from the given result
	 * set.
	 * 
	 * @param rs
	 *            result set containing student information
	 * @return a new student object with information extracted from the result
	 *         set
	 * @throws SQLException
	 */
	private static Student newStudentFromResultSet(ResultSet rs)
			throws SQLException {

		int schoolColumn = 0;
		try {
			schoolColumn = rs.findColumn("school");
		} catch (SQLException e) {
			schoolColumn = rs.findColumn("studentSchool");

		}

		Student student = new Student(rs.getString("userId"),
				rs.getString("password"), rs.getString("name"),
				rs.getString(schoolColumn), rs.getDouble("edollar"));
		return student;
	}

}
